package com.seu.vcampus.dao;

import com.seu.vcampus.vo.Book;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 图书数据访问类
 * 负责图书数据的CRUD操作
 * 
 * @author 虚拟校园开发团队
 * @since 1.0.0
 */
public class BookDao {
    private final Connection connection;
    
    public BookDao(Connection connection) {
        this.connection = connection;
        initializeTable();
    }
    
    /**
     * 初始化图书表
     */
    private void initializeTable() {
        try {
            String sql = "CREATE TABLE IF NOT EXISTS books (" +
                       "book_id VARCHAR(20) PRIMARY KEY," +
                       "title VARCHAR(100) NOT NULL," +
                       "author VARCHAR(50) NOT NULL," +
                       "publisher VARCHAR(50)," +
                       "isbn VARCHAR(20) UNIQUE," +
                       "stock INTEGER DEFAULT 0," +
                       "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                       "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                       ")";
            
            try (Statement stmt = connection.createStatement()) {
                stmt.execute(sql);
            }
            
            // 插入示例数据
            insertSampleData();
            
        } catch (SQLException e) {
            System.err.println("初始化图书表失败: " + e.getMessage());
        }
    }
    
    /**
     * 插入示例图书数据
     */
    private void insertSampleData() {
        try {
            // 检查是否已有数据
            String checkSql = "SELECT COUNT(*) FROM books";
            try (Statement checkStmt = connection.createStatement();
                 ResultSet rs = checkStmt.executeQuery(checkSql)) {
                if (rs.next() && rs.getInt(1) > 0) {
                    return; // 已有数据，不再插入
                }
            }
            
            // 插入示例图书
            Book[] sampleBooks = {
                new Book("B001", "Java编程思想", "Bruce Eckel", "机械工业出版社", "9787111213826", 5),
                new Book("B002", "深入理解计算机系统", "Randal E. Bryant", "机械工业出版社", "9787111323297", 3),
                new Book("B003", "算法导论", "Thomas H. Cormen", "机械工业出版社", "9787111407010", 2),
                new Book("B004", "设计模式", "Erich Gamma", "机械工业出版社", "9787111075752", 4),
                new Book("B005", "计算机网络", "Andrew S. Tanenbaum", "清华大学出版社", "9787302225061", 6),
                new Book("B006", "操作系统概念", "Abraham Silberschatz", "高等教育出版社", "9787040198568", 3),
                new Book("B007", "数据库系统概念", "Abraham Silberschatz", "机械工业出版社", "9787111252677", 4),
                new Book("B008", "C++ Primer", "Stanley B. Lippman", "电子工业出版社", "9787121031502", 2),
                new Book("B009", "Python编程：从入门到实践", "Eric Matthes", "人民邮电出版社", "9787115428028", 7),
                new Book("B010", "人工智能：一种现代方法", "Stuart Russell", "清华大学出版社", "9787302423283", 1)
            };
            
            String insertSql = "INSERT INTO books (book_id, title, author, publisher, isbn, stock) VALUES (?, ?, ?, ?, ?, ?)";
            try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
                for (Book book : sampleBooks) {
                    pstmt.setString(1, book.getBookId());
                    pstmt.setString(2, book.getTitle());
                    pstmt.setString(3, book.getAuthor());
                    pstmt.setString(4, book.getPublisher());
                    pstmt.setString(5, book.getIsbn());
                    pstmt.setInt(6, book.getStock());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
            
        } catch (SQLException e) {
            System.err.println("插入示例数据失败: " + e.getMessage());
        }
    }
    
    /**
     * 获取所有图书
     */
    public List<Book> getAllBooks() {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT book_id, title, author, publisher, isbn, stock, create_time, update_time FROM books ORDER BY title";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Book book = new Book();
                book.setBookId(rs.getString("book_id"));
                book.setTitle(rs.getString("title"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setIsbn(rs.getString("isbn"));
                book.setStock(rs.getInt("stock"));
                book.setCreateTime(rs.getTimestamp("create_time"));
                book.setUpdateTime(rs.getTimestamp("update_time"));
                books.add(book);
            }
            
        } catch (SQLException e) {
            System.err.println("获取图书列表失败: " + e.getMessage());
        }
        
        return books;
    }
    
    /**
     * 搜索图书
     */
    public List<Book> searchBooks(String keyword) {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT book_id, title, author, publisher, isbn, stock, create_time, update_time " +
                    "FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ? ORDER BY title";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            String likeKeyword = "%" + keyword + "%";
            pstmt.setString(1, likeKeyword);
            pstmt.setString(2, likeKeyword);
            pstmt.setString(3, likeKeyword);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Book book = new Book();
                    book.setBookId(rs.getString("book_id"));
                    book.setTitle(rs.getString("title"));
                    book.setAuthor(rs.getString("author"));
                    book.setPublisher(rs.getString("publisher"));
                    book.setIsbn(rs.getString("isbn"));
                    book.setStock(rs.getInt("stock"));
                    book.setCreateTime(rs.getTimestamp("create_time"));
                    book.setUpdateTime(rs.getTimestamp("update_time"));
                    books.add(book);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("搜索图书失败: " + e.getMessage());
        }
        
        return books;
    }
    
    /**
     * 根据ID获取图书
     */
    public Book getBookById(String bookId) {
        String sql = "SELECT book_id, title, author, publisher, isbn, stock, create_time, update_time " +
                    "FROM books WHERE book_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Book book = new Book();
                    book.setBookId(rs.getString("book_id"));
                    book.setTitle(rs.getString("title"));
                    book.setAuthor(rs.getString("author"));
                    book.setPublisher(rs.getString("publisher"));
                    book.setIsbn(rs.getString("isbn"));
                    book.setStock(rs.getInt("stock"));
                    book.setCreateTime(rs.getTimestamp("create_time"));
                    book.setUpdateTime(rs.getTimestamp("update_time"));
                    return book;
                }
            }
            
        } catch (SQLException e) {
            System.err.println("获取图书失败: " + e.getMessage());
        }
        
        return null;
    }
    
    /**
     * 更新图书库存
     */
    public boolean updateBookStock(String bookId, int newStock) {
        String sql = "UPDATE books SET stock = ?, update_time = CURRENT_TIMESTAMP WHERE book_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, newStock);
            pstmt.setString(2, bookId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("更新图书库存失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 添加图书
     */
    public boolean addBook(Book book) {
        String sql = "INSERT INTO books (book_id, title, author, publisher, isbn, stock) VALUES (?, ?, ?, ?, ?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, book.getBookId());
            pstmt.setString(2, book.getTitle());
            pstmt.setString(3, book.getAuthor());
            pstmt.setString(4, book.getPublisher());
            pstmt.setString(5, book.getIsbn());
            pstmt.setInt(6, book.getStock());
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("添加图书失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 更新图书信息
     */
    public boolean updateBook(Book book) {
        String sql = "UPDATE books SET title = ?, author = ?, publisher = ?, isbn = ?, stock = ?, update_time = CURRENT_TIMESTAMP WHERE book_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getPublisher());
            pstmt.setString(4, book.getIsbn());
            pstmt.setInt(5, book.getStock());
            pstmt.setString(6, book.getBookId());
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("更新图书信息失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 删除图书
     */
    public boolean deleteBook(String bookId) {
        String sql = "DELETE FROM books WHERE book_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("删除图书失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 检查图书ID是否存在
     */
    public boolean bookIdExists(String bookId) {
        String sql = "SELECT COUNT(*) FROM books WHERE book_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
            
        } catch (SQLException e) {
            System.err.println("检查图书ID失败: " + e.getMessage());
        }
        
        return false;
    }
}